import pandas as pd
import numpy as np
from pathlib import Path
import warnings

def read_all_leave_sheets(file_path):
    """读取请假数据文件中的所有sheet"""
    xlsx = pd.ExcelFile(file_path)
    all_leave_data = []
    
    for sheet_name in xlsx.sheet_names:
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        all_leave_data.append(df)
    
    # 合并所有sheet的数据
    if all_leave_data:
        return pd.concat(all_leave_data, ignore_index=True)
    return pd.DataFrame()

def check_time_overlap(travel_start, travel_end, leave_records):
    """检查出差时间是否在任一请假时间范围内"""
    if len(leave_records) == 0:
        return False
    
    try:
        travel_start = pd.to_datetime(travel_start).date()
        travel_end = pd.to_datetime(travel_end).date()
    except:
        return False
    
    # 遍历所有请假记录，只要有一条符合就返回True
    for _, leave in leave_records.iterrows():
        try:
            leave_start = pd.to_datetime(leave['leave_start_time']).date()
            leave_end = pd.to_datetime(leave['leave_end_time']).date()
            
            # 检查出差时间是否完全在请假时间范围内
            if (leave_start <= travel_start <= leave_end) and (leave_start <= travel_end <= leave_end):
                return True
        except:
            continue
    
    return False

def format_datetime_columns(df):
    """格式化DataFrame中的时间列为年月日格式"""
    datetime_columns = [
        '出发时间', '到达时间', '同组出发时间', '同组到达时间',
        'leave_start_time', 'leave_end_time'
    ]
    
    result_df = df.copy()
    for col in datetime_columns:
        if col in result_df.columns:
            try:
                # 先尝试转换为datetime
                result_df[col] = pd.to_datetime(result_df[col])
                # 然后转换为字符串格式
                result_df[col] = result_df[col].dt.strftime('%Y-%m-%d')
            except:
                # 如果转换失败，保持原样
                continue
    
    return result_df

def analyze_no_leave_records(df, purpose_keyword):
    """分析未请假记录"""
    try:
        # 确保数据框不为空
        if df.empty:
            print(f"警告：没有找到包含关键字 '{purpose_keyword}' 的数据")
            return pd.DataFrame()

        # 筛选包含关键字的记录
        filtered_df = df[df['出差目的名称'].str.contains(purpose_keyword, na=False)]
        
        if filtered_df.empty:
            print(f"警告：筛选后没有包含关键字 '{purpose_keyword}' 的数据")
            return pd.DataFrame()

        # 确保必要的列存在
        required_columns = ['单据编号', '人员编号', '同组出发时间', '同组到达时间']
        missing_columns = [col for col in required_columns if col not in filtered_df.columns]
        if missing_columns:
            print(f"错误：缺少必要的列: {', '.join(missing_columns)}")
            return pd.DataFrame()

        # 获取所有请假记录并确保每条记录的起止时间配对完整
        leave_data = []
        for _, row in filtered_df.iterrows():
            if pd.notna(row.get('leave_start_time')) and pd.notna(row.get('leave_end_time')):
                leave_data.append({
                    'leave_start_time': row['leave_start_time'],
                    'leave_end_time': row['leave_end_time']
                })
        
        leave_records = pd.DataFrame(leave_data)
        
        # 添加时间范围检查结果
        filtered_df['出差时间是否在请假范围内'] = filtered_df.apply(
            lambda row: '是' if check_time_overlap(
                row['同组出发时间'],
                row['同组到达时间'],
                leave_records
            ) else '否',
            axis=1
        )
        
        # 筛选出未在请假范围内的记录
        no_leave_records = filtered_df[filtered_df['出差时间是否在请假范围内'] == '否'].copy()
        
        if no_leave_records.empty:
            print(f"提示：没有找到未请假的{purpose_keyword}记录")
            return pd.DataFrame()

        # 格式化时间列
        no_leave_records = format_datetime_columns(no_leave_records)
        
        # 删除除了请假时间之外其他列都相同的重复行
        # 1. 找出所有列中除了请假时间的列
        other_columns = [col for col in no_leave_records.columns 
                        if col not in ['leave_start_time', 'leave_end_time']]
        
        # 2. 基于其他列进行去重，保留第一条记录
        no_leave_records = no_leave_records.drop_duplicates(subset=other_columns, keep='first')
        
        return no_leave_records

    except Exception as e:
        print(f"分析{purpose_keyword}未请假记录时出错: {str(e)}")
        return pd.DataFrame()

def main(once = False):
    """主函数：分析差旅报销与请假数据"""
    try:
        # 忽略警告
        warnings.filterwarnings('ignore')
        
        # 设置数据文件路径
        data_dir = Path("data")
        alldata_dir = data_dir / "alldata"
        
        # 确保目录存在
        alldata_dir.mkdir(parents=True, exist_ok=True)
        
        # 读取源数据文件
        print("正在读取源数据...")
        
        # 1. 读取差旅报销行程明细
        travel_detail_file = data_dir / "差旅报销行程明细(商旅).xlsx"
        if not travel_detail_file.exists():
            raise FileNotFoundError(f"文件不存在: {travel_detail_file}")
        travel_df = pd.read_excel(travel_detail_file)
        
        # 2. 读取商旅申请单据
        travel_application_file = data_dir / "商旅申请单据.xlsx"
        if not travel_application_file.exists():
            raise FileNotFoundError(f"文件不存在: {travel_application_file}")
        application_df = pd.read_excel(travel_application_file)
        
        # 3. 读取请假数据（所有sheet）
        leave_data_file = data_dir / "请假数据.xlsx"
        if not leave_data_file.exists():
            raise FileNotFoundError(f"文件不存在: {leave_data_file}")
        leave_df = read_all_leave_sheets(leave_data_file)
        
        print("正在处理数据...")
        
        # 确保时间列为datetime类型
        try:
            travel_df['出发时间'] = pd.to_datetime(travel_df['出发时间'])
        except:
            print("警告：出发时间格式转换失败")
        
        # 计算每组的最早和最晚出发时间
        group_times = travel_df.groupby(['单据编号', '人员编号']).agg({
            '出发时间': ['min', 'max']  # 同组最早和最晚出发时间
        }).reset_index()
        
        # 重命名列
        group_times.columns = ['单据编号', '人员编号', '同组出发时间', '同组到达时间']
        
        # 将组时间信息添加到原始数据中
        travel_df = pd.merge(
            travel_df,
            group_times,
            on=['单据编号', '人员编号'],
            how='left'
        )
        
        # 与商旅申请单据关联
        travel_df = pd.merge(
            travel_df,
            application_df[['对私报销单单据编号', '出行人员', '出差目的名称']],
            left_on=['单据编号', '人员编号'],
            right_on=['对私报销单单据编号', '出行人员'],
            how='left'
        )
        
        # 确保请假数据的时间列为datetime类型
        try:
            leave_df['leave_start_time'] = pd.to_datetime(leave_df['leave_start_time'])
            leave_df['leave_end_time'] = pd.to_datetime(leave_df['leave_end_time'])
        except:
            print("警告：请假时间格式转换失败")
        
        # 将结果与请假数据关联
        final_df = pd.merge(
            travel_df,
            leave_df[['apply_user_code', 'leave_start_time', 'leave_end_time']],
            left_on='人员编号',
            right_on='apply_user_code',
            how='left'
        )
        
        success = False
        
        # 分析外出会议未请假数据
        print("正在分析外出会议未请假数据...")
        meeting_no_leave = analyze_no_leave_records(final_df, "外部会议")

        # 分析外出培训未请假数据
        print("\n正在分析外出培训未请假数据...")
        training_no_leave = analyze_no_leave_records(final_df, "外部培训")

        if once:
            return meeting_no_leave, training_no_leave
        
        if not meeting_no_leave.empty:
            # 保存结果到alldata文件夹
            meeting_output_file = alldata_dir / "外出会议未请假分析结果.xlsx"
            print(f"正在保存外出会议分析结果到: {meeting_output_file}")
            meeting_no_leave.to_excel(meeting_output_file, index=False)
            success = True
            
            # 打印统计信息
            print(f"\n外出会议统计信息:")
            print(f"未请假记录数: {len(meeting_no_leave)}")
            print(f"涉及员工人数: {len(meeting_no_leave['人员编号'].unique())}")
        
        if not training_no_leave.empty:
            # 保存结果到alldata文件夹
            training_output_file = alldata_dir / "外出培训未请假分析结果.xlsx"
            print(f"正在保存外出培训分析结果到: {training_output_file}")
            training_no_leave.to_excel(training_output_file, index=False)
            success = True
            
            # 打印统计信息
            print(f"\n外出培训统计信息:")
            print(f"未请假记录数: {len(training_no_leave)}")
            print(f"涉及员工人数: {len(training_no_leave['人员编号'].unique())}")
        
        if success:
            print("\n分析完成！")
            return True
        else:
            print("\n没有找到任何未请假记录")
            return False
        
    except Exception as e:
        print(f"处理过程中出现错误: {str(e)}")
        return False

if __name__ == "__main__":
    main() 